# compare database
# sh compare.sh nanshan 119
####################configuration####################

NEED_CHECK_PROJECT_NAME=$1
environment=$2
if [[ -z $environment ]]
then
  environment=190
fi
name=`uname -s`
if [[ "${name}" = "Linux" ]]; then
currentHome=$( dirname "$(readlink -f  ${BASH_SOURCE[0]})" )
else
currentHome=$( dirname "$(greadlink -f  ${BASH_SOURCE[0]})" )
fi
YAPAI_HOME=$(dirname "$currentHome")
TARGET_DATABASE_HOST=(`cat ${YAPAI_HOME}/enviroment/${environment} | jq -r '.ip'`)
TARGET_DATABASE_USERNAME=(`cat ${YAPAI_HOME}/enviroment/${environment} | jq -r '.database_user_name'`)
TARGET_DATABASE_PASSWORD=(`cat ${YAPAI_HOME}/enviroment/${environment} | jq -r '.database_password'`)
PROJECT_DATABASE_NAME=(`cat ${YAPAI_HOME}/project/${NEED_CHECK_PROJECT_NAME} | jq -r '.database_name'`)
NEED_CHECK_PROJECT_NAME=(`cat ${YAPAI_HOME}/project/${NEED_CHECK_PROJECT_NAME} | jq -r '.project_name'`)
DATABASE_PORT=(`cat ${YAPAI_HOME}/enviroment/${environment} | jq -r '.database_port'`)
PARENT_MAIN_PROJECT_PATH="${HOME}/zxh/project/yapai/${NEED_CHECK_PROJECT_NAME}"
# MAIN_PROJECT_PATH=$(dirname `find ${PARENT_MAIN_PROJECT_PATH} -depth -name "src" | xargs ls -1d`)
MAIN_PROJECT_PATH=`find ${PARENT_MAIN_PROJECT_PATH} -depth -regex ".*src.*Application\.java" | awk -F 'src' '{print $1}'`
MAIN_PROJECT_PATH=${MAIN_PROJECT_PATH%?}
DATABASE_HOST="127.0.0.1"
DATABASE_USERNAME='root'
DATABASE_PASSWORD='123456'

# TARGET_DATABASE_HOST="192.168.0.190"
# TARGET_DATABASE_USERNAME='yapai'
# TARGET_DATABASE_PASSWORD='ypkj@1234'
DIR=$( dirname "$(readlink -f  ${BASH_SOURCE[0]})" )
####################configuration####################

cd ${MAIN_PROJECT_PATH}
git pull

BASE_FILE_SQL=(old ${NEED_CHECK_PROJECT_NAME})
for bfs in ${BASE_FILE_SQL[@]}
do
  DATABASE_NAME=${NEED_CHECK_PROJECT_NAME}_${RANDOM}
  # DATABASE_NAME=${NEED_CHECK_PROJECT_NAME}
  echo $bfs----$DATABASE_NAME
  mysql -h${DATABASE_HOST} -u${DATABASE_USERNAME} -p${DATABASE_PASSWORD} -e 'create database '"${DATABASE_NAME}"'' 2>&1 | sed -n '2,$p'
  files=()
  if [[ "${bfs}" == "${NEED_CHECK_PROJECT_NAME}" ]]
  then
    files[${#files[@]}]=${MAIN_PROJECT_PATH}/src/main/resources/sql/${bfs}.sql
  else
    files=(`find ${MAIN_PROJECT_PATH}/src/main/resources/sql/update -type f | xargs ls -1d | sort -r`)
    files[${#files[@]}]=${MAIN_PROJECT_PATH}/src/main/resources/sql/${bfs}.sql
  fi

  for (( i=${#files[@]};i>0;i--))
  do
    import_file=${files[i-1]}
    echo -e "\033[32m ${import_file} \033[0m"
    IMPORT_RESULT=`mysql -h${DATABASE_HOST} -u${DATABASE_USERNAME} -p${DATABASE_PASSWORD} -D${DATABASE_NAME} < ${import_file} 2>&1 | sed -n '2,$p'`
    # echo $IMPORT_RESULT
    errors_regex=`ls -1 ${DIR}/error`
    for regex in ${errors_regex[@]}
    do
      # REG='ERROR\s+1064.*:(.*)'
      REG=`cat ${DIR}/error/${regex} | sed -n '1p'`
      if [[ "${IMPORT_RESULT}" =~ ${REG} ]]
      then
        echo -e "----------------------------------\033[32m errors \033[0m----------------------------------"
        echo -e "\033[31m ${BASH_REMATCH[1]} \033[0m"
        echo -e "----------------------------------\033[32m errors \033[0m----------------------------------"
        # mysql -h${DATABASE_HOST} -u${DATABASE_USERNAME} -p${DATABASE_PASSWORD} -e 'drop database '"${DATABASE_NAME}"''
        # exit
      fi
    done
  done

  tables=`mysql -h${TARGET_DATABASE_HOST} -u${TARGET_DATABASE_USERNAME} -p${TARGET_DATABASE_PASSWORD} -e "use ${PROJECT_DATABASE_NAME};show tables;" 2>&1 | sed -n '3,$p' `

  MORE_TABLE=()
  DIFF_TABLE=()
  for table in ${tables[@]}
  do
    TABLE_INFO=`mysql -h${DATABASE_HOST} -u${DATABASE_USERNAME} -p${DATABASE_PASSWORD} -e 'select * from information_schema.tables where table_schema="'${DATABASE_NAME}'" and TABLE_NAME ="'${table}'" \G' 2>&1 | sed -n '2,$p' `
    if [[ -z ${TABLE_INFO} ]]
    then
      MORE_TABLE[${#MORE_TABLE[@]}]=${table}
      continue
    fi

    import_table_column_result=`mysql -h${DATABASE_HOST} -u${DATABASE_USERNAME} -p${DATABASE_PASSWORD} -e 'use '"${DATABASE_NAME}"';desc '"${table}"'' 2>&1 | sed -n '2,$p' | sort -k 1,1`
    target_table_column_result=`mysql -h${TARGET_DATABASE_HOST} -u${TARGET_DATABASE_USERNAME} -p${TARGET_DATABASE_PASSWORD} -e 'use '"${PROJECT_DATABASE_NAME}"';desc '"${table}"'' 2>&1 | sed -n '2,$p' | sort -k 1,1`
    
    if [[ $import_table_column_result != $target_table_column_result ]]
    then
      DIFF_TABLE[${#DIFF_TABLE[@]}]=${table}
      if [[ ! -d ${HOME}/temp/mysql/${DATABASE_NAME}/${table} ]]
      then
        mkdir -p ${HOME}/temp/mysql/${DATABASE_NAME}/${table}
        echo "$import_table_column_result" > ${HOME}/temp/mysql/${DATABASE_NAME}/${table}/import
        echo "$target_table_column_result" > ${HOME}/temp/mysql/${DATABASE_NAME}/${table}/target
      fi
    fi
  done

  echo "########################################report########################################"

  echo "----------------------------------------reduce----------------------------------------"
  for mt in ${MORE_TABLE[@]}
  do
    echo $mt
  done

  echo "----------------------------------------diff----------------------------------------"
  for table in ${DIFF_TABLE[@]}
  do
    echo -e "\033[32m ${table} \033[0m"
    echo "---------------------------**---------------------------"
    diff -w ${HOME}/temp/mysql/${DATABASE_NAME}/${table}/import ${HOME}/temp/mysql/${DATABASE_NAME}/${table}/target
    echo "---------------------------**---------------------------"
  done
  echo "########################################report########################################"
  rm -rf ${HOME}/temp/mysql/${DATABASE_NAME}
  # mysql -h${DATABASE_HOST} -u${DATABASE_USERNAME} -p${DATABASE_PASSWORD} -e 'drop database '"${DATABASE_NAME}"'' 2>&1 | sed -n '2,$p'
done
